﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using Utility;
namespace Service
{
    /// <summary>
    /// 功能：HW_Org类数据访问
    /// 创建人：Wilson
    /// 创建时间：2013-1-30   
    /// </summary>
    public class HW_OrgService : IDisposable
    {
        #region 公共
        private System.ComponentModel.Component component = new System.ComponentModel.Component();

        private bool disposed = false;

        ~HW_OrgService()
        {
            Dispose(false);
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {

                if (disposing)
                {

                    component.Dispose();
                }

                disposed = true;

            }
        }
        #endregion
		#region##MapperHW_Org
        public IList<HW_Org> MapperList(SqlDataReader reader)
         {
            IList<HW_Org> list = new List<HW_Org>();
            HW_Org obj=null;
            while (reader.Read())
            {
                obj = new HW_Org();
                obj.OrgCode = (string)reader["OrgCode"];
                obj.DistrictCode = (string)reader["DistrictCode"];
                obj.OrgName = (string)reader["OrgName"];
                obj.Version = (int)reader["Version"];
                obj.CityId = (string)reader["CityId"];
                list.Add(obj);
            }
            return list;
         }
        public HW_Org Mapper(SqlDataReader reader)
         {
            HW_Org obj=null;
            if (reader.Read())
            {
                obj = new HW_Org();
                obj.OrgCode = (string)reader["OrgCode"];
                obj.DistrictCode = (string)reader["DistrictCode"];
                obj.OrgName = (string)reader["OrgName"];
                obj.Version = (int)reader["Version"];
                obj.CityId = (string)reader["CityId"];


            }
            return obj;
         }
        #endregion
        #region##通过主键ID得到HW_Org
        /// <summary>
        /// 功能：通过ID得到HW_Org
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
        public HW_Org GetById(string orgcode)
        {
            string sql = "select * from HW_Org  where OrgCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",orgcode)
            };
            
            SqlDataReader reader=SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql,sp);
            HW_Org entity= Mapper(reader);
            //HW_OrgDetail detail = new HW_OrgDetail();
            //HW_OrgDetailService service = new HW_OrgDetailService();
            //detail=service.Mapper(reader);
            //entity.hw_OrgDetail = detail;
            return entity;
        }
        #endregion
		
		#region##通过主键ID删除HW_Org
        /// <summary>
        /// 功能：通过主键ID删除HW_Org
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(string orgcode)
		{
			int i = 0;
			string sql = "delete from HW_Org where OrgCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",50)
            };
            i= SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
			return i;
		}
        /// <summary>
        /// 功能：通过主键ID删除HW_Org
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(SqlTransaction transaction,string orgcode)
		{
			int i = 0;
			string sql = "delete from HW_Org where OrgCode = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",orgcode)
            };
            i= SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
			return i;
		}
		#endregion			
		
        #region##添加HW_Org
        /// <summary>
        /// 功能：添加HW_Org
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(HW_Org hw_org)
        {
            int result = 0;
            string sql = "insert into HW_Org(OrgCode,DistrictCode,OrgName,Version,CityId) values(@OrgCode,@DistrictCode,@OrgName,@Version,@CityId)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@OrgCode",hw_org.OrgCode),
				new SqlParameter("@DistrictCode",hw_org.DistrictCode),
				new SqlParameter("@OrgName",hw_org.OrgName),
				new SqlParameter("@Version",hw_org.Version),
				new SqlParameter("@CityId",hw_org.CityId)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
    
        /// <summary>
        /// 功能：添加HW_Org
         /// <summary>
        /// 功能：添加HW_Org
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(SqlTransaction transaction,HW_Org hw_org)
        {
            int result = 0;
            string sql = "insert into HW_Org(OrgCode,DistrictCode,OrgName,Version,CityId) values(@OrgCode,@DistrictCode,@OrgName,@Version,@CityId)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@OrgCode",hw_org.OrgCode),
				new SqlParameter("@DistrictCode",hw_org.DistrictCode),
				new SqlParameter("@OrgName",hw_org.OrgName),
				new SqlParameter("@Version",hw_org.Version),
				new SqlParameter("@CityId",hw_org.CityId)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##得到HW_Org集合
		/// <summary>
        /// 功能：得到HW_Org集合
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>       
        /// <returns></returns>
		public IList<HW_Org> GetList()
		{
			string sql = "select * from HW_Org";			
			SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql);
            return MapperList(reader);
		}
        public DataTable GetSubList(int pageIndex, int pageSize, out int count, string pdistrictCode)
        {
            string sql = @";WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
select Top(@pageSize) * from  HW_Org b left join HW_OrgDetail c on b.OrgCode=c.OrgCode
                        INNER join m d on b.DistrictCode=d.DistrictCode LEFT join  m e on d.ParentDistrictCode=e.DistrictCode
                        where b.orgcode not in (select TOP(@pageSize*@pageIndex) orgcode from  HW_Org y
                        INNER JOIN m z ON y.DistrictCode=z.DistrictCode order by y.orgcode)order by b.orgcode
                        ";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@pageIndex",pageIndex),
				new SqlParameter("@pageSize",pageSize),
				new SqlParameter("@districtCode",pdistrictCode),
            };
            SqlParameter[] sp2 = new SqlParameter[]
            {       
				new SqlParameter("@districtCode",pdistrictCode),
            };
            string sql2 = @"
;WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
select count(XX_UserName) from HW_OrgUser x inner join HW_Org y on x.OrgCode=y.OrgCode inner join m on y.DistrictCode=m.DistrictCode";
            count = (int)SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, sql2, sp2);
            DataTable result = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return result;
        }
        public DataTable Search(string district, string searchString)
        {
            string sql = @"
                   ;WITH m(DistrictCode,NAME,ParentDistrictCode) AS(
	SELECT R.DistrictCode,R.NAME,R.ParentDistrictCode FROM dbo.HW_District R WHERE DistrictCode=@districtcode 
	UNION ALL SELECT child.DistrictCode,child.NAME,child.ParentDistrictCode FROM m parent, HW_District child WHERE parent.DistrictCode=child.ParentDistrictCode
)
select * from  HW_Org b  left join HW_OrgDetail c on b.OrgCode=c.OrgCode
                        INNER join m d on b.DistrictCode=d.DistrictCode LEFT join  m e on d.ParentDistrictCode=e.DistrictCode
                        where b.OrgName like @searchString or b.OrgCode like @searchString order by b.orgcode
                        ";
            SqlParameter[] sp = new SqlParameter[]
            {   
                new SqlParameter("@districtcode",district),
				new SqlParameter("@searchString","%"+searchString+"%"),
            };
            return SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
        }
		#endregion
		
		#region##修改HW_Org
        /// <summary>
        /// 功能：修改HW_Org
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_Org(HW_Org hw_org)
        {
            int result = 0;
            string sql = "update HW_Org set OrgCode = @OrgCode,DistrictCode = @DistrictCode,OrgName = @OrgName,Version = @Version,CityId = @CityId where OrgCode = @OrgCode";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@OrgCode",hw_org.OrgCode),
				new SqlParameter("@DistrictCode",hw_org.DistrictCode),
				new SqlParameter("@OrgName",hw_org.OrgName),
				new SqlParameter("@Version",hw_org.Version),
				new SqlParameter("@CityId",hw_org.CityId)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：修改HW_Org
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModHW_Org(SqlTransaction transaction,HW_Org hw_org)
        {
            int result = 0;
            string sql = "update HW_Org set OrgCode = @OrgCode,DistrictCode = @DistrictCode,OrgName = @OrgName,Version = @Version,CityId = @CityId where OrgCode = @OrgCode";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@OrgCode",hw_org.OrgCode),
				new SqlParameter("@DistrictCode",hw_org.DistrictCode),
				new SqlParameter("@OrgName",hw_org.OrgName),
				new SqlParameter("@Version",hw_org.Version),
				new SqlParameter("@CityId",hw_org.CityId)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##统计HW_Org
		/// <summary>
        /// 修改HW_Org
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
		public int SelectCount()
		{
			int i = 0;
            string sql = "select count(*) from HW_Org";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
		}
		/// <summary>
        /// 修改HW_Org
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int SelectCountById()
        {
            int i=0;
            string sql="select count(OrgCode) from HW_Org";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
        }
		#endregion
        #region##自定义HW_Org
        #endregion
                
    }
}
